package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import beans.Box;
import beans.Database;

public class BoxDAO {

	private static final int SUM_THREAD_IN_BOX = 2;

	public static ArrayList<Box> selectAll() {
		ArrayList<Box> list = new ArrayList<>();
		Connection conn = null;
		PreparedStatement pre = null;
		ResultSet rs = null;

		try {
			conn = Database.connect();
			pre = conn.prepareStatement("SELECT * FROM box");
			rs = pre.executeQuery();

			while (rs.next()) {
				Box box = new Box(rs.getInt("id"), rs.getString("name"),
						rs.getString("note"));
				box.setThreads(ThreadDAO.selectThreadInBox(box.getId(),
						SUM_THREAD_IN_BOX));
				list.add(box);
			}

		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return list;
	}

	public static Box selectById(int boxId) {
		Box box = null;
		Connection conn = null;
		PreparedStatement pre = null;
		ResultSet rs = null;

		try {
			conn = Database.connect();
			pre = conn.prepareStatement("SELECT name FROM box WHERE id=?");
			pre.setInt(1, boxId);
			rs = pre.executeQuery();

			if (rs.next()) {
				box = new Box(boxId, rs.getString("name"));
			}

		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return box;
	}

	public static int selectSumThreadInBox(int boxId) {
		int result = -1;
		Connection conn = null;
		PreparedStatement pre = null;
		ResultSet rs = null;

		try {
			conn = Database.connect();
			pre = conn
					.prepareStatement("SELECT count(*) FROM thread WHERE box_id=?");
			pre.setInt(1, boxId);
			rs = pre.executeQuery();

			if (rs.next()) {
				result = rs.getInt(1);
			}
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}
}
